package com.csust.travel.mapper;

import com.csust.travel.entity.Books;
import com.csust.travel.entity.NewUsers;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface NewUsersMapper {
    @Select("select * from new_users order by update_time desc")
    List<NewUsers> findAllItem();
    @Select("select * from new_users where role = #{role}")
    List<NewUsers> findUsersByRole(String role);
    @Select("select * from new_users where email = #{string} or username like concat('%',#{string},'%') or realname like concat('%',#{string},'%')")
    List<NewUsers> findByQuery(String string);

    @Select("select * from new_users where (email = #{string} or username like concat('%',#{string},'%') or realname like concat('%',#{string},'%')) and role = #{na}")
    List<NewUsers> findByFilters(String string,String na);

    @Select("select * from new_users where username = #{username}")
    NewUsers findUserByUsername(String username);

    @Delete("delete from new_users where id = #{id}")
    void delItem(Integer id);

    @Insert("insert into new_users (username,password,email,role) values(#{username},#{password},#{email},#{role})")
    void addUser(NewUsers newUsers);

    @Select("select * from new_users where id = #{id}")
    NewUsers selectById(Integer id);

    @Update("update new_users set avatar = #{avatar} where id = #{id}")
    void setAvatar(String avatar,Integer id);

    @Update("update new_users set username=#{username},realname=#{realname},email =#{email},college=#{college},major=#{major},sex=#{sex} ,phonenumber=#{phonenumber},qqnumber=#{qqnumber},bio=#{bio} where id = #{id}")
    void editItem(NewUsers item);


    @Update("update new_users set password = #{newPasswd} where id = #{id}")
    void UpdatePasswd(String newPasswd,Integer id);

    @Update("update new_users set update_time = now() where id = #{id}")
    void updateTime(Integer id);


    @Update("update new_users set password = #{newPasswd} where email = #{email}")
    void findPwd(String newPasswd,String email);


    @Select("SELECT COUNT(*) AS count\n" +
            "FROM books\n" +
            "WHERE owner = #{owner}")
    Integer getTotalBook(Integer owner);

    @Select("SELECT DISTINCT new_users.username\n" +
            "FROM books\n" +
            "JOIN new_users ON books.owner = new_users.id\n" +
            "WHERE books.owner = #{id};")
    String getUsername(Integer id);

    @Select("SELECT DISTINCT new_users.qqnumber\n" +
            "FROM books\n" +
            "JOIN new_users ON books.owner = new_users.id\n" +
            "WHERE books.owner = #{id};")
    String getQqnumber(Integer id);

    @Select("SELECT DISTINCT new_users.avatar\n" +
            "FROM books\n" +
            "JOIN new_users ON books.owner = new_users.id\n" +
            "WHERE books.owner = #{id};")
    String getAvatar(Integer id);

    @Select("SELECT DISTINCT new_users.college\n" +
            "FROM books\n" +
            "JOIN new_users ON books.owner = new_users.id\n" +
            "WHERE books.owner = #{id};")
    String getCollege(Integer id);


    @Select("SELECT DISTINCT new_users.major\n" +
            "FROM books\n" +
            "JOIN new_users ON books.owner = new_users.id\n" +
            "WHERE books.owner = #{id};")
    String getMajor(Integer id);
}
